﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;

public partial class UserControl_ControlHistoryDailyRestore : System.Web.UI.UserControl
{
    ClassExecute.ClassExecute dt;
    string str = webapp4U.Configuration.CurrentConnectionString;
    string error = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadDistributor();
            txtFromDate.Text = DateTime.Now.AddDays(-1).ToString("dd/MM/yyyy");
            txtToDate.Text = DateTime.Now.ToString("dd/MM/yyyy");
            chkFromDateToDate.Checked = true;
            LoadGrid();
        }
        HideMessageBox();
    }
    protected void imgBtnAdd_Click(object sender, ImageClickEventArgs e)
    {
        LoadGrid();
    }
    protected void grvIntxd_PageIndexChanging1(object sender, GridViewPageEventArgs e)
    {
        grvIntxd.PageIndex = e.NewPageIndex;
        LoadGrid();
    }



    void LoadDistributor()
    {
        dt = new ClassExecute.ClassExecute();
        try
        {
            DataTable db = dt.Get_Data_DataTable(str, ref error, "TTr_Branlist_GetDistributorByParentSite", CommandType.StoredProcedure, new SqlParameter("@SiteCode", Session["sitecode"].ToString()));
            dt.BindDropDownList(db, drlDistributor, "Sitecode", "Sitename");
        }
        catch (Exception ex)
        {
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }

    void LoadGrid()
    {
        int LocTheoNgay = 0;
        if (chkFromDateToDate.Checked)
            LocTheoNgay = 1;
        dt = new ClassExecute.ClassExecute();
        try
        {
            DataTable db = dt.Get_Data_DataTable(str, ref error, "TTr_UploadFile_GetListDeletedDateNow", CommandType.StoredProcedure,
                new SqlParameter("@SiteCode", Session["sitecode"].ToString()),
                new SqlParameter("@Distributor", drlDistributor.SelectedValue.ToString()),
                new SqlParameter("@From", txtFromDate.Text),
                new SqlParameter("@To", txtToDate.Text),
                new SqlParameter("@LocTheoNgay", LocTheoNgay));
            if (db.Rows.Count == 0)
            {
                grvIntxd.DataSource = null;
            }
            else
            {
                grvIntxd.DataSource = db;
            }
            grvIntxd.DataBind();
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    void ShowMessageBox(string text)
    {
        MessageBox.Text = text;
        MessageBox.ShowOnPageLoad = true;
    }
    void HideMessageBox()
    {
        MessageBox.Text = "";
        MessageBox.ShowOnPageLoad = false;
    }
    protected void grvIntxd_RowCommand1(object sender, GridViewCommandEventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        try
        {
            if (e.CommandName == "Restore")
            {
                if (Session["user"] != null)
                {
                    GridViewRow grv = ((Control)e.CommandSource).NamingContainer as GridViewRow;
                    Label lblFileNameDestination = (Label)grv.FindControl("lblFileNameDestination");
                    Label lblFileNameOrginaly = (Label)grv.FindControl("lblFileNameOrginaly");
                    Label lblSitecode = (Label)grv.FindControl("lblSitecode");

                    string YyyyMm = DateTime.Now.ToString("yyyyMM");
                    string pathFolder = @"UserUpload\ImportedFileExcel\" + YyyyMm;
                    string savepath = "";
                    savepath = webapp4U.Globals.PhysicalApplicationPath() + pathFolder;
                    if (!Directory.Exists(savepath))
                        Directory.CreateDirectory(savepath);
                    string physicalPathFolder = @"/UserUpload/ImportedFileExcel/" + YyyyMm + "/";
                    //Rename and Move fiel to  ImportedFileExcel/
                    string filePathSource = webapp4U.Globals.PhysicalApplicationPath() + @"/UserUpload/DeletedFileExcel/" + YyyyMm + "/" + lblFileNameDestination.Text;
                    string filePathDes = webapp4U.Globals.PhysicalApplicationPath() + physicalPathFolder + lblFileNameDestination.Text;
                    File.Move(filePathSource, filePathDes);

                    ////Import data to table Temp
                    Excel2Sql(physicalPathFolder, lblFileNameDestination.Text, lblSitecode.Text);
                    int kq = dt.ExecuteSQl_int(str, "TTr_UploadFile_Restore", CommandType.StoredProcedure, ref error,
                        new SqlParameter("@FileNameDestination", lblFileNameDestination.Text),
                        new SqlParameter("@PathFolder", pathFolder),
                        new SqlParameter("@UserUpload", Session["user"].ToString()));
                    if (kq==1)
                    {

                        dt = new ClassExecute.ClassExecute();
                        //Kiểm tra format dữ liệu.
                        bool intresult = false;
                        intresult = dt.ExecuteSQl_bool(str, CommandType.StoredProcedure, "TTr_InsertDataImportDKSHDemo", ref error, new SqlParameter("@UserName",Session["user"].ToString()));
                        if (intresult)
                        {
                            LoadGrid();
                            ShowMessageBox("Khôi phục dữ liệu đã xóa thành công");                            
                            dt.ExecuteSQl_bool(str, CommandType.StoredProcedure, "D_History_Insert", ref error,
                                new SqlParameter("@UserName", Session["user"].ToString()), new SqlParameter("@OrdinaryFileName", lblFileNameOrginaly.Text),
                                new SqlParameter("@DestinationFileName", lblFileNameDestination.Text), new SqlParameter("@Action", Action.Restore),
                                new SqlParameter("@Time", DateTime.Now));                            
                        }
                        else
                        {
                            ShowMessageBox("Khôi phục dữ liệu đã xóa thất bại");
                            File.Move(filePathDes, filePathSource);
                        }
                    }                    
                }
                else
                {
                    ShowMessageBox("Bạn hãy đăng nhập lại hệ thống");
                }
            }            
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }


    protected void Excel2Sql(string PhysicalPathFolder, string fileName, string strSiteCodeDistributor)
    {
        //string strSource = Globals.PhysicalApplicationPath() + "/UserUpload/BaoCaoExcel/" + fileName;

        string strSource = webapp4U.Globals.PhysicalApplicationPath() + PhysicalPathFolder + fileName;
        //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSource + ";Extended Properties=Excel 12.0;Persist Security Info=False";

        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strSource + ";Extended Properties=Excel 12.0;Persist Security Info=False;");

        try
        {
            dt = new ClassExecute.ClassExecute();
            dt.ExecuteSQl_bool(str, CommandType.StoredProcedure, "TTr_TempImportDKSH_Delete", ref error, new SqlParameter("@UserName", Session["user"].ToString()));
            ////string strSiteCode = "FM_00000003"; //Session["sitecode"].ToString();

            string sql = "select KHO,MSVT,TENVT,MKH,TENKH,TENKH1,DC,QHT,MAQUAN,CTYPE,CHANNEL,LAND,DISTRICT,INVOICE,NGAY,TDV,QTY";
            sql = sql + ",DGLE,TT,TTV,NGUYENGIA,'" + strSiteCodeDistributor + "' as SiteCode,'" + fileName + "' as FileName from [DKSH$] ";

            OleDbCommand ocmd = new OleDbCommand(sql, oconn);
            oconn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter(ocmd);
            DataTable dbTest = new DataTable();
            da.Fill(dbTest);
            oconn.Close();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(str))
            {
                bulkCopy.BatchSize = 500;
                bulkCopy.NotifyAfter = 1000;
                //bulkCopy.SqlRowsCopied +=
                //    new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                bulkCopy.DestinationTableName = "dbo.TempImportDKSH" + Session["user"].ToString();
                bulkCopy.WriteToServer(dbTest);

            }


        }
        catch (DataException ex)
        {
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
            return;

        }

    }

    protected void chkFromDateToDate_CheckedChanged(object sender, EventArgs e)
    {
        txtFromDate.Enabled = chkFromDateToDate.Checked;
        txtToDate.Enabled = chkFromDateToDate.Checked;
    }
}
